﻿using System;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using System.Text;

namespace Shuba.Data
{
    public class FlagFreeze
    {
        public List<Shuba.Model.FlagFreezeInfo> GetList(string fieldName)
        {
            List<Shuba.Model.FlagFreezeInfo> _list = new List<Shuba.Model.FlagFreezeInfo>();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT * FROM TBL_MS_FLAG_FREEZE WHERE FIELD LIKE '%" + fieldName + "%'";

            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);
            using (IDataReader dr = mydb.ExecuteReader(mycomm))
            {
                while (dr.Read())
                {
                    _list.Add(new Shuba.Model.FlagFreezeInfo(dr.GetInt16(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6), dr.GetDateTime(7)));
                }
                dr.Close();
            }
            return _list;
        }

        public Shuba.Model.FlagFreezeInfo Get(Int16 flag, string field)
        {
            Shuba.Model.FlagFreezeInfo _list = new Shuba.Model.FlagFreezeInfo();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT * FROM TBL_MS_FLAG_FREEZE WHERE RTRIM(LTRIM(FLAG)) = @FLAG AND RTRIM(LTRIM(FIELD)) = @FIELD";
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycomm, "@FLAG", DbType.Int16, flag);
            mydb.AddInParameter(mycomm, "@FIELD", DbType.String, field);

            using (IDataReader dr = mydb.ExecuteReader(mycomm))
            {
                while (dr.Read())
                {
                    _list = new Shuba.Model.FlagFreezeInfo(dr.GetInt16(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6), dr.GetDateTime(7));
                }
                dr.Close();
            }
            return _list;
        }

        public int Save(Shuba.Model.FlagFreezeInfo bi)
        {
            Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand(@"UPDATE TBL_MS_FLAG_FREEZE_TMP SET 
            	                 FLAG = @FLAG
                                ,FIELD = @FIELD
                                ,ENABLED = @ENABLED
                                ,IS_ACTIVE = @IS_ACTIVE
                                ,ENTRY_BY = @ENTRY_BY
                                ,ENTRY_DATE = @ENTRY_DATE
                                ,UPDATE_BY = @UPDATE_BY
                                ,UPDATE_DATE = @UPDATE_DATE
                                ,ACT_TYPE = @ACT_TYPE
                                WHERE FLAG = @FLAG AND FIELD = @FIELD

                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_MS_FLAG_FREEZE_TMP(FLAG
                                ,FIELD
                                ,ENABLED
                                ,IS_ACTIVE
                                ,ENTRY_BY
                                ,ENTRY_DATE
                                ,UPDATE_BY
                                ,UPDATE_DATE
                                ,ACT_TYPE
                                ) 
                                VALUES(@FLAG
                                ,@FIELD
                                ,@ENABLED
                                ,@IS_ACTIVE
                                ,@ENTRY_BY
                                ,@ENTRY_DATE
                                ,@UPDATE_BY
                                ,@UPDATE_DATE
                                ,@ACT_TYPE
                                );");

            db.AddInParameter(cmd, "@FLAG", DbType.Int16, bi.FLAG);
            db.AddInParameter(cmd, "@FIELD", DbType.String, bi.FIELD);
            db.AddInParameter(cmd, "@ENABLED", DbType.String, bi.ENABLED);
            db.AddInParameter(cmd, "@IS_ACTIVE", DbType.String, bi.IS_ACTIVE);
            db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, bi.ENTRY_BY);
            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, bi.ENTRY_DATE);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, bi.UPDATE_BY);
            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, bi.UPDATE_DATE);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, bi.ACT_TYPE);

            return db.ExecuteNonQuery(cmd);
        }

        public List<Shuba.Model.FlagFreezeInfo> GetListApproval(string UpdateBy)
        {
            try
            {
                List<Shuba.Model.FlagFreezeInfo> info = new List<Shuba.Model.FlagFreezeInfo>();

                Database mydb = DBConnection.CreateDatabase();

                string mysql = @"SELECT FLAG,FIELD,ENABLED,IS_ACTIVE,ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE,ACT_TYPE
                                 FROM TBL_MS_FLAG_FREEZE_TMP WHERE (RTRIM(UPDATE_BY) <> '" + UpdateBy + "')";
                DbCommand mycomm = mydb.GetSqlStringCommand(mysql);

                using (IDataReader dr = mydb.ExecuteReader(mycomm))
                {
                    while (dr.Read())
                    {
                        info.Add(new Shuba.Model.FlagFreezeInfo(dr.GetInt16(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6), dr.GetDateTime(7), dr.GetString(8)));
                    }
                    dr.Close();
                }
                return info;
            }
            catch (Exception e)
            {
                e.ToString();
            }
            return null;
        }

        public Shuba.Model.FlagFreezeInfo GetApprovalByID(Int16 flag, string field)
        {
            Shuba.Model.FlagFreezeInfo _list = new Shuba.Model.FlagFreezeInfo();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT * FROM TBL_MS_FLAG_FREEZE_TMP WHERE RTRIM(LTRIM(FLAG)) = @FLAG 
	                            AND RTRIM(LTRIM(FIELD)) = @FIELD";
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycomm, "@FLAG", DbType.Int16, flag);
            mydb.AddInParameter(mycomm, "@FIELD", DbType.String, field.Trim());

            using (IDataReader dr = mydb.ExecuteReader(mycomm))
            {
                while (dr.Read())
                {
                    _list = new Shuba.Model.FlagFreezeInfo(dr.GetInt16(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6), dr.GetDateTime(7), dr.GetString(8));
                }
                dr.Close();
            }
            return _list;
        }

        public int Approve(string ActType, Int16 flag, string field)
        {
            int success = 0;
            try
            {
                Database mydb = DBConnection.CreateDatabase();
                DbCommand mycomm = mydb.GetStoredProcCommand("FLAG_FREEZE_APPROVAL");
                mydb.AddInParameter(mycomm, "@ACT_TYPE", DbType.String, ActType);
                mydb.AddInParameter(mycomm, "@flag", DbType.Int16, flag);
                mydb.AddInParameter(mycomm, "@field", DbType.String, field);

                success = mydb.ExecuteNonQuery(mycomm);
            }
            catch (Exception e) { e.ToString(); }
            return success;
        }

        public void Reject(Int16 flag, string field)
        {
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"DELETE TBL_MS_FLAG_FREEZE_TMP WHERE RTRIM(LTRIM(FLAG)) = @FLAG 
	                            AND RTRIM(LTRIM(FIELD)) = @FIELD";
            DbCommand mycmd = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycmd, "@FLAG", DbType.Int16, flag);
            mydb.AddInParameter(mycmd, "@FIELD", DbType.String, field);
            mydb.ExecuteNonQuery(mycmd);
        }
    }
}